﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Globalization;
using System.Data;

namespace NewContracts
{
    class XDBOp
    {
        /* ************************************************
         * Data Member
         * ************************************************/
        private String m_strConn;

        /* ************************************************
         * Public Function
         * ************************************************/
        public XDBOp()
        {
            Properties.Settings config = Properties.Settings.Default;
            m_strConn = config.sqlConnectString;
            if (m_strConn == "")
            {
                throw new Exception("[NG] Connect Database Failed. Application will close");
            }
        }

        /* *************************************************
         * Public Function
         * *************************************************/
        private void execPFindExistRecords(DataRow in_dataRow)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(m_strConn))
                {
                    sqlConn.Open();
                    SqlCommand sqlCmd = sqlConn.CreateCommand();
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    sqlCmd.CommandText = "h_sp_find_exist_records";

                    //Create input parameter
                    SqlParameter logicalNumber = sqlCmd.Parameters.Add("@in_logicalNumber", SqlDbType.VarChar);
                    logicalNumber.Direction = ParameterDirection.Input;
                    logicalNumber.Value = in_dataRow[0].ToString().Trim();

                    SqlParameter name = sqlCmd.Parameters.Add("@in_name", SqlDbType.VarChar);
                    name.Direction = ParameterDirection.Input;
                    name.Value = in_dataRow[1].ToString().Trim();

                    SqlParameter counterparty = sqlCmd.Parameters.Add("@in_counterparty", SqlDbType.VarChar);
                    counterparty.Direction = ParameterDirection.Input;
                    counterparty.Value = in_dataRow[2].ToString().Trim();

                    SqlParameter maturityDate = sqlCmd.Parameters.Add("@in_maturityDate", SqlDbType.VarChar);
                    maturityDate.Direction = ParameterDirection.Input;
                    maturityDate.Value = Convert.ToDateTime(in_dataRow[3].ToString()).ToShortDateString();

                    SqlParameter totAmount = sqlCmd.Parameters.Add("@in_totAmount", SqlDbType.VarChar);
                    totAmount.Direction = ParameterDirection.Input;
                    totAmount.Value = in_dataRow[4].ToString().Trim();

                    SqlParameter paidStatus = sqlCmd.Parameters.Add("@in_paidSatus", SqlDbType.VarChar);
                    paidStatus.Direction = ParameterDirection.Input;
                    paidStatus.Value = in_dataRow[5].ToString().Trim();

                    SqlParameter localccy = sqlCmd.Parameters.Add("@in_localccy", SqlDbType.VarChar);
                    localccy.Direction = ParameterDirection.Input;
                    localccy.Value = in_dataRow[6].ToString().Trim();

                    SqlParameter localAmount = sqlCmd.Parameters.Add("@in_localAmount", SqlDbType.VarChar);
                    localccy.Direction = ParameterDirection.Input;
                    localccy.Value = in_dataRow[7].ToString().Trim();

                    SqlParameter rate = sqlCmd.Parameters.Add("@in_rate", SqlDbType.VarChar);
                    rate.Direction = ParameterDirection.Input;
                    rate.Value = in_dataRow[8].ToString().Trim();

                    SqlParameter currency = sqlCmd.Parameters.Add("@in_currency", SqlDbType.VarChar);
                    currency.Direction = ParameterDirection.Input;
                    currency.Value = in_dataRow[9].ToString().Trim();

                    SqlParameter instrument = sqlCmd.Parameters.Add("@in_instrument", SqlDbType.VarChar);
                    instrument.Direction = ParameterDirection.Input;
                    instrument.Value = in_dataRow[10].ToString().Trim();
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("XDbOp: " + ex.Message);
            }
        }

        /*!
         * @brief : yesterday don't have, today have
         */
        public DataSetNewDropContracts findNewRecords(DataSet in_dsPayment, DataSet in_dsAllFEC)
        {
            String xlsCounterparty, xlsUpdateDate, xlsMaturityDate, xlsTotAmount;
            DataSetNewDropContracts dsNewRecords = new DataSetNewDropContracts();

            foreach (DataRow row  in in_dsPayment.Tables[0].Rows)
            {
                //Search each row in Paid&Unpaid.xls,
                //if the record(xls) has matched in db, then do nothing
                //if the record(xls) has not found in db, then it's new
                xlsCounterparty = row["Counterparty"].ToString().Trim();
                xlsUpdateDate = row["UpdateDate"].ToString().Trim();
                xlsMaturityDate = row["MaturityDate"].ToString().Trim();
                xlsTotAmount = row["TotAmount"].ToString().Trim();
                String dbUpdateDate = (Convert.ToDateTime(xlsUpdateDate)).AddDays(-1).ToString("yyyy-MM-dd");
                String dbMaturityDate = (Convert.ToDateTime(xlsMaturityDate)).ToString("yyyy-MM-dd");
                String sqlQueryUpdateDate = "SELECT * FROM TPayment WHERE " +
                    "Counterparty='" + xlsCounterparty + "' AND " +
                    "MaturityDate='" + dbMaturityDate + "' AND " +
                    "UpdateDate='" + dbUpdateDate + "' AND " +
                    "TotalAmount='" + xlsTotAmount + "'";
                using (SqlConnection sqlConn = new SqlConnection(m_strConn))
                {
                    try
                    {
                        sqlConn.Open();
                        using (SqlCommand sqlCmd = new SqlCommand(sqlQueryUpdateDate, sqlConn))
                        {
                            SqlDataReader sqlReader = sqlCmd.ExecuteReader();
                            if (!sqlReader.Read())
                            {
                                DataRow newRow = dsNewRecords.Tables[0].NewRow();

                                newRow["Number"] = row["LogicalNumber"];
                                newRow["Counterparty"] = row["Counterparty"].ToString();
                                newRow["ClientName"] = row["Name"].ToString();
                                newRow["PaidStatus"] = row["PaidStatus"].ToString();
                                newRow["ValueDate"] = row["MaturityDate"];
                                newRow["BaseAmountLeft"] = row["TotAmount"];
                                newRow["BaseCcy"] = row["Currency"];
                                newRow["ForwardRate"] = row["Rate"];
                                newRow["Instrument"] = row["Instrument"];
                                newRow["Rate"] = row["Rate"];
                                newRow["CCY2"] = row["Currency"];
                                newRow["UpdateDate"] = row["UpdateDate"];
                                dsNewRecords.Tables[0].Rows.Add(newRow);
                            }
                          
                            sqlReader.Close();
                        }
                        sqlConn.Close();
                    }
                    catch (System.Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
            return dsNewRecords;
        }

        /*
         * Drop Out Contracts: yesterday have, today don't have
         */
        public DataSetNewDropContracts findDropOutRecords(DataSet in_dsPayment, DateTime in_date)
        {
            DataSetNewDropContracts dsDropOutRecords = new DataSetNewDropContracts();
            DateTime previousDate = in_date.AddDays(-1);
            String strDate = String.Format("{0:yyyy-MM-dd}", previousDate);
            String sqlPreviousDate = "SELECT * FROM TPayment WHERE UpdateDate='" + strDate + "'";

            try
            {
                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sqlPreviousDate, m_strConn))
                {
                    DataSet dsFromDb = new DataSet();
                    dataAdapter.Fill(dsFromDb,"TPayment");
                    foreach (DataRow dbRow in dsFromDb.Tables[0].Rows)
                    {
                        bool isFind = false;
                        foreach (DataRow xlsRow in in_dsPayment.Tables[0].Rows)
                        {
                            if(0==String.Compare(dbRow["Counterparty"].ToString().Trim(), xlsRow["Counterparty"].ToString().Trim(),true))
                            {
                                if (0 == String.Compare(dbRow["MaturityDate"].ToString().Trim(), xlsRow["MaturityDate"].ToString().Trim(), true))
                                {
                                    if(0 == String.Compare(dbRow["TotAmount"].ToString().Trim(),xlsRow["TotAmount"].ToString().Trim(),true))
                                    {
                                        isFind = true;
                                        break;
                                    }
                                }
                            }//End-Of-XLS-Row
                        }
                        if (!isFind)
                        {
                            dsDropOutRecords.Tables[0].Rows.Add(dbRow);
                        }
                    }//End-Of-DbRow
                }
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return dsDropOutRecords;
        }
    }
}